'''
版权：Copyright (c) 2019 China

创建日期：Thursday July 25th 2019
创建者：ymq(ymq) - <<email>>

修改日期: Thursday, 25th July 2019 9:00:01 am
修改者: ymq(ymq) - <<email>>

说明
 1、财务管理相关接口
'''
import datetime
import copy
import uuid
import pandas as pd
from server.pao_python.pao.service.data.mongo_db import MongoService, MongoFilter, C, N, F, as_date
from server.pao_python.pao.data import string_to_date, date_to_string
from ...service.common import get_info, get_current_user_id
from ...service.buss_pub.bill_manage import BillManageService, OperationType, Status, TypeId
from ...models.financial_manage import FinancialBook, FinancialAccount, FinancialRecord, FinancialSubject, FinancialVoucher, FinancialEntry, FinancialCharge, FinancialSetSubjectItem
from server.pao_python.pao.data import dataframe_to_list
from ...service.mongo_bill_service import MongoBillFilter
from ...service.constant import AccountType, AccountStatus, PayType, PayStatue, plat_id


class FinancialService(MongoService):
    '''财务管理服务'''
    book_id = '16e726be-b405-11e9-9f09-983b8f0bcd67'

    def __init__(self, db_addr, db_port, db_name, db_user, db_pwd, inital_password, session):
        self.db_addr = db_addr
        self.db_port = db_port
        self.db_name = db_name
        self.db_user = db_user
        self.db_pwd = db_pwd
        self.inital_password = inital_password
        self.session = session
        self.bill_manage_server = BillManageService(
            self.db_addr, self.db_port, self.db_name, self.db_user, self.db_pwd, self.inital_password, self.session)

    def input_user_name_list(self, condition):
        '''下拉框-经济主体姓名'''
        keys = ['user_id']
        values = self.get_value(condition, keys)
        # 过滤器配置
        _filter = MongoBillFilter()
        _filter.match_bill(C('id') == values['user_id'])\
               .project({'_id': 0, 'key': '$id', 'text': '$name'})

        res = self.query(_filter, 'PT_User')
        print('用户列表', res)
        return res

    def input_account_book_list(self, condition):
        '''下拉框-账簿名称'''
        keys = ['book_id']
        values = self.get_value(condition, keys)
        # 过滤器配置
        _filter = MongoBillFilter()
        _filter.match_bill(C('id') == values['book_id'])\
               .project({'_id': 0, 'key': '$id', 'text': '$account_book_name'})

        res = self.query(_filter, FinancialBook().name)
        return res

    def input_account_list(self, condition):
        '''下拉框-账户名称'''
        keys = ['account_id']
        values = self.get_value(condition, keys)
        # 过滤器配置
        _filter = MongoBillFilter()
        _filter.match(C('id') == values['account_id'])\
               .project({'_id': 0, 'key': '$id', 'text': '$account_name'})

        res = self.query(_filter, 'PT_Financial_Account')
        return res

    def input_subject_list(self, condition):
        '''下拉框-会计科目'''
        keys = ['subject_id']
        values = self.get_value(condition, keys)
        # 过滤器配置
        _filter = MongoBillFilter()
        _filter.match_bill((C('id') == values['subject_id']) & (C('book_id') == self.book_id))\
               .project({'_id': 0, 'key': '$id', 'text': '$subject_name', 'code': '$subject_code'})

        res = self.query(_filter, FinancialSubject().name)
        return res

    def __get_initial_subject_data(self, book_id):
        '''初始化科目数据'''
        _filter = MongoBillFilter()
        _filter.match_bill(C('book_id') == 'initial')
        init_data = self.query(_filter, FinancialSubject().name)
        init_df = pd.DataFrame(init_data)
        init_df = init_df[['id', 'subject_code', 'subject_name',
                           'subject_type', 'remark', 'balance', 'super_subject_id']]
        init_df = init_df.fillna('')
        init_df['book_id'] = book_id
        res = []
        for i in range(len(init_df)):
            new_id = str(uuid.uuid1())
            tep_id = init_df['id'].iloc[i]
            init_df = init_df.replace([tep_id], [new_id])
        for j in range(len(init_df)):
            tep = dict(init_df.iloc[j, :])
            tep['subject_code'] = int(tep['subject_code'])
            tep['balance'] = float(tep['balance'])
            res.append(tep)
        return res

    def update_account_book_data(self, data):
        '''新增/更新账簿数据'''
        res = 'Fail'
        if 'id' not in list(data.keys()):
            data['set_date'] = datetime.datetime.now()
            data['id'] = str(uuid.uuid1())
            bill_id = self.bill_manage_server.add_bill(
                OperationType.add.value, TypeId.financial.value, data, FinancialBook().name)
            init_subject = self.__get_initial_subject_data(data['id'])
            for i in init_subject:
                bill_id = self.bill_manage_server.add_bill(
                    OperationType.add.value, TypeId.financial.value, i, FinancialSubject().name)
            if bill_id:
                res = 'Success'
        else:
            bill_id = self.bill_manage_server.add_bill(
                OperationType.update.value, TypeId.financial.value, data, FinancialBook().name)
            if bill_id:
                res = 'Success'
        return res

    def get_account_book_list(self, org_list, condition, page, count):
        '''获取账簿信息列表'''
        keys = ['user_name', 'id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill(C('id') == values['id'])\
               .inner_join_bill('PT_User', 'user_id', 'id', 'user_info')\
               .match(C('user_info.name').like(values['user_name']) & (C('organization_id').inner(org_list)))\
               .project({'_id': 0, 'user_name': '$user_info.name', 'user_id': '$user_info.id', 'account_book_name': 1, 'type_book': 1, 'set_date': 1, 'settle_account_date': 1, 'id': 1})
        res = self.page_query(_filter, FinancialBook().name, page, count)
        return res

    def get_account_list(self, org_list, condition, page, count):
        '''获取账户信息列表'''
        keys = ['id', 'name']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill((C('id') == values['id']) & (C('name') == values['name']))\
            .lookup_bill('PT_User', 'user_id', 'id', 'main_user')\
            .lookup_bill('PT_User', 'agent_user_id', 'id', 'agent_user')\
            .match((C('organization_id').inner(org_list)))\
            .add_fields({'main_user_name': '$main_user.name', 'agent_user_name': '$agent_user.name'})\
            .project({'_id': 0, 'main_user': 0, 'agent_user': 0})
        res = self.page_query(_filter, FinancialAccount().name, page, count)
        return res

    def get_user_account_sequence_all(self, condition, page, count):
        '''获取用户所属账户顺序信息'''
        user_id = get_current_user_id(self.session)
        _filter = MongoBillFilter()
        _filter.match_bill((C('user_id') == user_id))\
            .project({'_id': 0})
        res = self.page_query(
            _filter, 'PT_Financial_Agent_Pay_Sequence', page, count)
        return res

    def update_account_data(self, data):
        '''更新/新增账户信息数据'''
        res = 'Fail'
        if(data['account_type'] == '真实账户'):
            obj = {'name': data['extend_account_name'],
                   'card_number': data['extend_card_number']}
            data['extend_info'] = obj
            del data['extend_account_name']
            del data['extend_card_number']
            print(data)
        if 'id' not in list(data.keys()):
            data['balance'] = 0
            bill_id = self.bill_manage_server.add_bill(
                OperationType.add.value, TypeId.financial.value, data, FinancialAccount().name)
            if bill_id:
                res = 'Success'
        else:
            bill_id = self.bill_manage_server.add_bill(
                OperationType.update.value, TypeId.financial.value, data, FinancialAccount().name)
            if bill_id:
                res = 'Success'
        return res

    def update_account_data_list(self, data):
        '''更新/新增账户顺序'''
        user_id = get_current_user_id(self.session)
        _filter = MongoBillFilter()
        _filter.match_bill((C('user_id') == user_id))\
            .project({'_id': 0})
        res = self.page_query(
            _filter, 'PT_Financial_Agent_Pay_Sequence', None, None)

        if(len(res['result']) > 0):
            item = {'user_id': user_id, 'agent_pay_sequence': data,
                    'id': res['result'][0]['id']}
            self.bill_manage_server.add_bill(
                OperationType.update.value, TypeId.financial.value, item, 'PT_Financial_Agent_Pay_Sequence')
        else:
            item = {'user_id': user_id, 'agent_pay_sequence': data}
            self.bill_manage_server.add_bill(
                OperationType.add.value, TypeId.financial.value, item, 'PT_Financial_Agent_Pay_Sequence')
        return 'Success'

    def __change_account_balance(self, account_id, number):
        '''在账户流水发生变化时修改账户表的余额'''
        _filter = MongoFilter()
        _filter.match((C('bill_status') == 'valid') & (C('id') == account_id))
        init_data = self.query(_filter, FinancialAccount().name)
        init_data = init_data[0]
        tep = init_data['balance']+number
        init_data['balance'] = tep
        bill_id = self.bill_manage_server.add_bill(
            OperationType.update.value, TypeId.financial.value, init_data, FinancialAccount().name)
        return bill_id

    def save_account_payment_data(self, data):
        '''保存账户付款数据'''
        res = 'Fail'
        date = datetime.datetime.now()
        data['date'] = date
        data['borrow_loan'] = 'loan'
        data['is_return'] = False
        bill_id = self.bill_manage_server.add_bill(
            OperationType.add.value, TypeId.financial.value, data, FinancialRecord().name)
        change = self.__change_account_balance(
            data['account_id'], (-1)*float(data['amount']))
        if bill_id and change:
            res = 'Success'
        return res

    def save_account_receive_data(self, data):
        '''保存账户收款数据'''
        res = 'Fail'
        date = datetime.datetime.now()
        data['date'] = date
        data['borrow_loan'] = 'borrow'
        data['is_return'] = False
        bill_id = self.bill_manage_server.add_bill(
            OperationType.add.value, TypeId.financial.value, data, FinancialRecord().name)
        change = self.__change_account_balance(
            data['account_id'], float(data['amount']))
        if bill_id and change:
            res = 'Success'
        return res

    def save_account_transfer_data(self, data):
        '''保存账户转账数据'''
        res = 'Fail'
        date = datetime.datetime.now()
        data['date'] = date
        data['is_return'] = False
        pay_account_id = data['pay_account_id']
        del data['pay_account_id']
        data['account_id'] = data.pop('receive_account_id')
        data['borrow_loan'] = 'borrow'
        data1 = copy.deepcopy(data)
        data['account_id'] = pay_account_id
        data['borrow_loan'] = 'loan'
        print('data1: ', data1)
        print('data: ', data)
        bill_id1 = self.bill_manage_server.add_bill(
            OperationType.add.value, TypeId.financial.value, data1, FinancialRecord().name)
        change1 = self.__change_account_balance(
            data1['account_id'], (-1)*float(data1['amount']))
        bill_id2 = self.bill_manage_server.add_bill(
            OperationType.add.value, TypeId.financial.value, data, FinancialRecord().name)
        change2 = self.__change_account_balance(
            data['account_id'], float(data['amount']))
        if bill_id1 and bill_id2 and change1 and change2:
            res = 'Success'
        return res

    def get_account_flow_list(self, org_list, condition, page, count):
        '''获取账户流水列表'''
        if 'date_range' in list(condition.keys()):
            condition['start_date'] = condition['date_range'][0]
            condition['end_date'] = condition['date_range'][1]
        keys = ['start_date', 'end_date', 'abstract', 'account_id', 'id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill((C('id') == values['id'])
                           & (C('date') >= as_date(values['start_date']))
                           & (C('date') <= as_date(values['end_date']))
                           & (C('account_id') == values['account_id'])
                           & C('abstract').like(values['abstract'])
                           & (C('organization_id').inner(org_list)))\
            .lookup_bill(FinancialAccount().name, 'account_id', 'id', 'acc_name')\
            .project({'_id': 0, 'account': '$acc_name.account_name', 'abstract': 1, 'amount': 1, 'date': 1, 'id': 1, 'borrow_loan': 1, 'original_voucher': 1, 'account_id': 1, 'return_id': 1, 'red_reason': 1, 'is_return': 1})
        res = self.page_query(_filter, FinancialRecord().name, page, count)
        return res

    def update_subject_data(self, data):
        '''编辑会计科目数据'''
        res = 'Fail'
        data['book_id'] = self.book_id
        if 'id' in list(data.keys()):
            _a = data.pop('add_sub_data', data)  # data为当前科目数据
            bill_id = self.bill_manage_server.add_bill(
                OperationType.update.value, TypeId.financial.value, data, FinancialSubject().name)
        else:
            bill_id = self.bill_manage_server.add_bill(
                OperationType.add.value, TypeId.financial.value, data, FinancialSubject().name)
        if bill_id:
            res = 'Success'
        return res

    def get_subject_list(self, condition, page, count):
        '''获取会计科目列表'''
        keys = ['book_id', 'subject_type', 'subject_name', 'id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill((C('book_id') == self.book_id)
                           & (C('id') == values['id'])
                           & (C('subject_type').like(values['subject_type']))
                           & (C('subject_name').like(values['subject_name'])))\
            .lookup_bill(FinancialSubject().name, 'super_subject_id', 'id', 'super_sub')\
            .lookup_bill(FinancialSubject().name, 'id', 'super_subject_id', 'add_sub_data')\
            .add_fields({'super_subject': '$super_sub.subject_name'})\
            .project({'_id': 0, 'super_sub._id': 0, 'add_sub_data._id': 0})
        print(_filter.filter_objects)
        res = self.page_query(_filter, FinancialSubject().name, page, count)
        return res

    def del_subject_data(self, ids):
        '''删除科目，被引用的科目不允许删除'''
        res = 'Fail'
        for i in ids:
            _filter = MongoFilter()
            _filter.match((C('bill_status') == 'valid')
                          & (C('super_subject_id') == i))
            res = self.query(_filter, FinancialSubject().name)
            if len(res) < 1:
                bill_id = self.bill_manage_server.add_bill(
                    OperationType.delete.value, TypeId.financial.value, {'id': i}, FinancialSubject().name)
                if bill_id:
                    res = 'Success'
            else:
                res = '被引用不可删除'
        return res

    def get_voucher_list(self, condition, page, count):
        '''获取记账凭证列表'''
        if 'date_range' in list(condition.keys()) and len(condition['date_range']) == 2:
            condition['start_date'] = as_date(condition['date_range'][0])
            condition['end_date'] = as_date(
                condition['date_range'][1])+datetime.timedelta(days=1)
        keys = ['start_date', 'end_date', 'abstract', 'id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match((C('bill_status') == 'valid')
                      & (C('date') > values['start_date'])
                      & (C('date') < values['end_date'])
                      & (C('id') == values['id'])
                      & (C('abstract').like(values['abstract']))
                      & (C('book_id') == self.book_id))\
            .lookup_bill(FinancialEntry().name, 'entry_list', 'id', 'add_sub_data')\
            .project({'_id': 0, 'add_sub_data._id': 0})
        res = self.page_query(_filter, FinancialVoucher().name, page, count)
        return res

    def add_account_voucher(self, data):
        '''增加记账凭证'''
        res = 'Fail'
        data['book_id'] = self.book_id
        entry_data = data['add_sub_data']
        data['date'] = as_date(data['date'])
        entry_ids = [str(uuid.uuid1()) for i in range(len(entry_data))]
        data.pop('add_sub_data')
        data['entry_list'] = entry_ids
        bill_id = self.bill_manage_server.add_bill(
            OperationType.add.value, TypeId.financial.value, data, FinancialVoucher().name)
        for i in range(len(entry_data)):
            tep_insert = entry_data[i]
            tep_insert['id'] = entry_ids[i]
            self.bill_manage_server.add_bill(
                OperationType.add.value, TypeId.financial.value, tep_insert, FinancialEntry().name)
        if bill_id:
            res = 'Success'
        return res

    def query_account_voucher_list(self, condition, page, count):
        '''查看记账凭证分录列表'''
        keys = ['ids']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match((C('bill_status') == 'valid')
                      & (C('id').inner(values['ids'])))\
            .inner_join_bill(FinancialEntry().name, 'entry_list', 'id', 'entry_info')\
            .inner_join_bill(FinancialSubject().name, 'entry_info.entry_subject', 'id', 'subject_info')\
            .project({'_id': 0, 'date': 1, 'voucher': 1, 'entry_abstract': '$entry_info.entry_abstract', 'entry_subject': '$subject_info.subject_name', 'borrow_loan': '$entry_info.borrow_loan', 'amount': '$entry_info.amount'})
        res = self.page_query(_filter, FinancialVoucher().name, page, count)
        return res

    def return_account_data(self, data):
        '''红冲账户记录数据'''
        res = 'Fail'
        update_data = {}
        update_data['id'] = data['id']
        update_data['is_return'] = True
        data['return_id'] = data.pop('id')
        tep = data['borrow_loan']
        if tep == 'borrow':
            tep = 'loan'
        else:
            tep = 'borrow'
        data['borrow_loan'] = tep
        data.pop('account')
        data['date'] = datetime.datetime.now()
        bill_id1 = self.bill_manage_server.add_bill(
            OperationType.add.value, TypeId.financial.value, data, FinancialRecord().name)
        bill_id2 = self.bill_manage_server.add_bill(
            OperationType.update.value, TypeId.financial.value, update_data, FinancialRecord().name)
        if bill_id1 and bill_id2:
            res = 'Success'
        return res

    def return_account_voucher(self, data):
        '''红冲记账凭证数据'''
        res = 'Fail'
        _filter = MongoFilter()
        _filter.match((C('bill_status') == 'valid') & (C('id') == data['id']))\
               .project({'_id': 0})
        res = self.query(_filter, FinancialVoucher().name)
        return_data = res[0]
        return_data['return_id'] = return_data.pop('id')
        return_data['red_reason'] = data['red_reason']
        bill_id = self.bill_manage_server.add_bill(
            OperationType.add.value, TypeId.financial.value, return_data, FinancialVoucher().name)
        if bill_id:
            res = 'Success'
        return res

############################################银行对账接口##################################################

    def add_charge_back_detil_data(self, data):
        '''获取扣款明细数据'''
        # 存储扣款明细数据，添加列
        res = 'Fail'
        for i in data:
            i['is_charge'] = '未扣款'
            i['date'] = datetime.datetime.now()
        bill_id = self.bill_manage_server.add_bill(
            OperationType.add.value, TypeId.financial.value, data, FinancialCharge().name)
        if bill_id:
            res = 'Success'
        return res

    def get_not_set_item(self):
        '''获取未设置对应会计科目的服务项目类型'''
        _filter = MongoBillFilter()
        _filter.match(C('bill_status') == 'valid')\
               .group({'type': '$item_type'})\
               .lookup_bill(FinancialSetSubjectItem().name, 'type', 'item_type', 'unset')\
               .match(C('unset') == [])\
               .project({'type': 1})
        res = self.query(_filter, FinancialCharge().name)
        return res

    def set_subject_item(self, data):
        '''服务项目类型与会计科目映射关系设置'''
        res = 'Fail'
        bill_id = self.bill_manage_server.add_bill(
            OperationType.add.value, TypeId.financial.value, data, FinancialSetSubjectItem().name)
        if bill_id:
            res = 'Success'
        return res

    def export_receipt_item(self, condition, page, count):
        '''查询收付款记录'''
        if 'date_range' in list(condition.keys()):
            condition['start_date'] = condition['date_range'][0]
            condition['end_date'] = condition['date_range'][1]
        keys = ['account_type', 'start_date', 'end_date']
        values = self.get_value(condition, keys)
        user_id = get_current_user_id(self.session)
        _filter = MongoBillFilter()
        _filter.match_bill((C('user_id') == user_id) & (C('account_type') == values['account_type']))\
               .inner_join_bill('PT_Financial_Account_Record', 'id', 'account_id', 'record_info')\
               .match((C('date') > values['start_date']) & (C('date') < values['end_date']))\
               .sort({'date': -1})\
               .project({'_id': 0, 'id': '$record_info.id', 'abstract': '$record_info.abstract', 'amount': '$record_info.amount', 'date': '$record_info.date'})
        res = self.page_query(_filter, 'PT_Financial_Account', page, count)
        return res

    def export_receipt_details_data(self, record_id):
        '''查询收付款详情订单记录,返回对象'''
        _filter = MongoBillFilter()
        _filter.match_bill(C('id') == record_id)\
               .inner_join_bill('PT_Financial_Account', 'account_id', 'id', 'account_info')\
               .project({'_id': 0, 'date': 1, 'account_type': '$account_info.account_type', 'balance': 1, 'abstract': 1, 'amount': 1})
        res = self.page_query(_filter, 'PT_Financial_Account_Record', 1, 1)
        res = res[0]
        if res['amount'] > 0:
            res['type'] = '收入'
        else:
            res['type'] = '支出'
        return res

    def get_divide_record_list(self, condition, page, count):
        '''查询收入分成记录列表'''
        if 'date_range' in list(condition.keys()):
            condition['start_date'] = condition['date_range'][0]
            condition['end_date'] = condition['date_range'][1]
        keys = ['is_distributed', 'start_date', 'end_date',
                'organization_name', 'organization_type']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill((C('is_distributed') == values['is_distributed'])
                           & (C('date') > as_date(values['start_date']))
                           & (C('date') < as_date(values['end_date'])))\
               .inner_join_bill('PT_User', 'user_id', 'id', 'user_info')\
               .match(C('user_info.name').like(values['organization_name']) & (C('user_info.orgnaization_info.personnel_type') == values['organization_type']))\
               .project({'_id': 0, 'organization_name': '$user_info.name', 'is_distributed': 1, 'divide_percent': 1, 'amount': 1, 'date': 1, 'financial_id': 1, 'abstract': 1})
        res = self.page_query(
            _filter, 'PT_Financial_Divide_Record', page, count)
        return res

    def get_subsidy_service_provider_list(self, condition, page, count):
        '''获取服务商补贴账户账单'''
        if 'date_range' in list(condition.keys()):
            condition['start_date'] = condition['date_range'][0]
            condition['end_date'] = condition['date_range'][1]
        keys = ['organization_name', 'start_date', 'end_date']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill((C('date') > as_date(values['start_date']))
                           & (C('date') < as_date(values['end_date']))
                           & (C('pay_type') == PayType.subsidy)
                           & (C('pay_status') == PayStatue.success))\
               .inner_join_bill('PT_Financial_Account', 'receiver_account_id', 'id', 'account_info')\
               .inner_join_bill('PT_User', 'account_info.user_id', 'id', 'user_info')\
               .match(C('user_info.name').like(values['organization_name']))\
               .project({'_id': 0, 'organization_name': '$user_info.name', 'amount': 1, 'date': 1, 'financial_id': 1, 'abstract': '$abstract'})
        res = self.page_query(
            _filter, 'PT_Financial_Receivable_Process', page, count)
        return res

    def get_service_provider_settlement(self, condition, page=None, count=None):
        res = {'result': []}
        keys = ['org_id', 'social_worker_id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        if 'month' in condition.keys() and len(condition['month']) == 7:
            # str类型,格式：yyyymm
            month = int(condition['month'][5:])
            year = int(condition['month'][0:4])
            start = datetime.datetime.now()
            end = datetime.datetime.now()
            if month < 12:
                start = datetime.datetime(year, month, 1, 0, 0, 0)
                end = datetime.datetime(
                    year, month + 1, 1, 23, 59, 59)-datetime.timedelta(days=1)
            else:
                start = datetime.datetime(year, month, 1, 0, 0, 0)
                end = datetime.datetime(
                    year + 1, 1, 1, 23, 59, 59)-datetime.timedelta(days=1)
            _filter.match_bill((C('create_date') > start)
                               & (C('create_date') < end))
        admin_list = N()
        if 'social_worker_id' in condition.keys():
            social_worker = ''
            # 查询该社工局对应及其下所有行政区划id数组
            _filter_worker = MongoBillFilter()
            _filter_worker.match_bill((C('id') == values['social_worker_id']))\
                .project({'_id': 0})
            res_social_worker = self.query(_filter_worker, 'PT_User')
            if len(res_social_worker) > 0:
                social_worker = res_social_worker[0]['name']
                admin_id = res_social_worker[0]['admin_area_id']
                _filter_admin = MongoBillFilter()
                _filter_admin.match_bill(C('id') == admin_id)\
                    .graph_lookup('PT_Administration_Division', '$id', 'id', 'parent_id', 'admin_info')\
                    .unwind('admin_info')\
                    .match(C('admin_info.bill_status') == 'valid')\
                    .project({'_id': 0, 'admin_id': '$admin_info.id'})
                query_res = self.query(
                    _filter_admin, 'PT_Administration_Division')
                admin_list = [t['admin_id'] for t in query_res]
                admin_list.append(admin_id)
        else:
            social_worker = '全部'
        # 增加社工局返回字段
        _filter.add_fields({
            "social_worker": social_worker
        })
        _filter.match((C('bill_status') == 'valid'))\
            .lookup_bill('PT_Service_Order', 'order_id', 'id', 'order')\
            .match_bill((C('order.buy_type') == '补贴账户') | (C('order.buy_type') == '慈善账户'))\
            .add_fields({'service_provider_id': '$order.service_provider_id', 'order_purchaser_id': '$order.purchaser_id'})\
            .lookup_bill('PT_User', 'service_provider_id', 'id', 'user')\
            .match_bill((C('user.organization_info.personnel_category') == '服务商') & (C('service_provider_id') == values['org_id']))\
            .lookup_bill('PT_User', 'order_purchaser_id', 'id', 'elder')\
            .match_bill((C('elder.admin_area_id').inner(admin_list)))\
            .add_fields({'service_provider_name': '$user.name'})\
            .add_fields({'current_year': self.ao.to_string(self.ao.year('$create_date'))})\
            .add_fields({'current_month': self.ao.to_string(self.ao.month('$create_date'))})\
            .add_fields({'month': self.ao.concat([('$current_year'), '-', ('$current_month')])})\
            .project({'month': 1, 'valuation_amount': 1, 'service_provider_name': 1, 'social_worker': 1})\
            .group(
            {
                'service_provider_name': '$service_provider_name',
                'year_month': '$month',
                'social_worker': '$social_worker'
            },
            [{
                'order_quantity': self.ao.summation(1)
            }, {
                'subsidy_amount_total': self.ao.summation('$valuation_amount')
            }])\
            .add_fields({'district_burden': ((F('subsidy_amount_total'))*(0.5)).f})\
            .add_fields({'town_burden': ((F('subsidy_amount_total'))*(0.5)).f})\
            .add_fields({'state': '待结算'})\
            .sort({'year_month': 1})
        res_old_age = self.query(_filter, 'PT_Service_Record')
        res['result'] = res_old_age
        return res

    def get_service_provider_settlement_total(self, condition):
        res = {'result': []}
        keys = ['org_id', 'social_worker_id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        if 'month' in condition.keys() and len(condition['month']) == 7:
            # str类型,格式：yyyy/mm
            month = int(condition['month'][5:])
            year = int(condition['month'][0:4])
            start = datetime.datetime.now()
            end = datetime.datetime.now()
            if month < 12:
                start = datetime.datetime(year, month, 1, 0, 0, 0)
                end = datetime.datetime(
                    year, month + 1, 1, 23, 59, 59)-datetime.timedelta(days=1)
            else:
                start = datetime.datetime(year, month, 1, 0, 0, 0)
                end = datetime.datetime(
                    year + 1, 1, 1, 23, 59, 59)-datetime.timedelta(days=1)
            _filter.match_bill((C('create_date') > start)
                               & (C('create_date') < end))
        admin_list = N()
        if 'social_worker_id' in condition.keys():
            social_worker = ''
            # 查询该社工局对应及其下所有行政区划id数组
            _filter_worker = MongoBillFilter()
            _filter_worker.match_bill((C('id') == values['social_worker_id']))\
                .project({'_id': 0})
            res_social_worker = self.query(_filter_worker, 'PT_User')
            if len(res_social_worker) > 0:
                social_worker = res_social_worker[0]['name']
                admin_id = res_social_worker[0]['admin_area_id']
                _filter_admin = MongoBillFilter()
                _filter_admin.match_bill(C('id') == admin_id)\
                    .graph_lookup('PT_Administration_Division', '$id', 'id', 'parent_id', 'admin_info')\
                    .unwind('admin_info')\
                    .match(C('admin_info.bill_status') == 'valid')\
                    .project({'_id': 0, 'admin_id': '$admin_info.id'})
                query_res = self.query(
                    _filter_admin, 'PT_Administration_Division')
                admin_list = [t['admin_id'] for t in query_res]
                admin_list.append(admin_id)

        _filter.match((C('bill_status') == 'valid'))\
            .lookup_bill('PT_Service_Order', 'order_id', 'id', 'order')\
            .match_bill((C('order.buy_type') == '补贴账户') | (C('order.buy_type') == '慈善账户'))\
            .add_fields({'service_provider_id': '$order.service_provider_id', 'order_purchaser_id': '$order.purchaser_id'})\
            .lookup_bill('PT_User', 'service_provider_id', 'id', 'user')\
            .match_bill((C('user.organization_info.personnel_category') == '服务商') & (C('service_provider_id') == values['org_id']))\
            .lookup_bill('PT_User', 'order_purchaser_id', 'id', 'elder')\
            .match_bill((C('elder.admin_area_id').inner(admin_list)))\
            .add_fields({'current_year': self.ao.to_string(self.ao.year('$create_date'))})\
            .add_fields({'current_month': self.ao.to_string(self.ao.month('$create_date'))})\
            .add_fields({'month': self.ao.concat([('$current_year'), '-', ('$current_month')])})\
            .project({'month': 1, 'valuation_amount': 1, 'service_provider_id': 1})\
            .group(
            {
                'month': '$month',
                'service_provider_id': '$service_provider_id'
            },
            [{
                'order_quantity_total': self.ao.summation(1)
            }, {
                'valuation_amount_t': self.ao.summation('$valuation_amount')
            }])\
            .group(
            {
                'year_month': '$month',
            },
            [{
                'service_provider_quantity': self.ao.summation(1)
            }, {
                'order_quantity': self.ao.summation('$order_quantity_total')
            }, {
                'valuation_amount_total': self.ao.summation('$valuation_amount_t')
            }]
        )\
            .add_fields({'district_burden': ((F('valuation_amount_total'))*(0.5)).f})\
            .add_fields({'town_burden': ((F('valuation_amount_total'))*(0.5)).f})\
            .add_fields({'subsidy_amount_total': '$valuation_amount_total'})
        if 'social_worker_id' in condition.keys():
            # 增加社工局返回字段
            _filter.add_fields({
                "social_worker": social_worker
            })
        else:
            _filter.add_fields({
                "social_worker": '全部'
            })
        _filter.sort({'year_month': 1})
        res_old_age = self.query(_filter, 'PT_Service_Record')
        res['result'] = res_old_age
        return res

    def get_service_provider_record_settlement(self, condition, page=None, count=None):
        keys = ['month', 'org_id', 'social_worker_id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        if 'month' in condition.keys() and len(condition['month']) == 7:
            # str类型,格式：yyyy/mm
            month = int(condition['month'][5:])
            year = int(condition['month'][0:4])
            start = datetime.datetime.now()
            end = datetime.datetime.now()
            if month < 12:
                start = datetime.datetime(year, month, 1, 0, 0, 0)
                end = datetime.datetime(
                    year, month + 1, 1, 23, 59, 59)-datetime.timedelta(days=1)
            else:
                start = datetime.datetime(year, month, 1, 0, 0, 0)
                end = datetime.datetime(
                    year + 1, 1, 1, 23, 59, 59)-datetime.timedelta(days=1)
            _filter.match_bill((C('create_date') > start)
                               & (C('create_date') < end))
        admin_list = N()
        if 'social_worker_id' in condition.keys():
            social_worker = ''
            # 查询该社工局对应及其下所有行政区划id数组
            _filter_worker = MongoBillFilter()
            _filter_worker.match_bill((C('id') == values['social_worker_id']))\
                .project({'_id': 0})
            res_social_worker = self.query(_filter_worker, 'PT_User')
            if len(res_social_worker) > 0:
                social_worker = res_social_worker[0]['name']
                admin_id = res_social_worker[0]['admin_area_id']
                _filter_admin = MongoBillFilter()
                _filter_admin.match_bill(C('id') == admin_id)\
                    .graph_lookup('PT_Administration_Division', '$id', 'id', 'parent_id', 'admin_info')\
                    .unwind('admin_info')\
                    .match(C('admin_info.bill_status') == 'valid')\
                    .project({'_id': 0, 'admin_id': '$admin_info.id'})
                query_res = self.query(
                    _filter_admin, 'PT_Administration_Division')
                admin_list = [t['admin_id'] for t in query_res]
                admin_list.append(admin_id)
        sgj_list = []
        _filter_worker = MongoBillFilter()
        _filter_worker.match_bill((C('organization_info.personnel_category') == '街道'))\
            .project({'_id': 0})
        res_social_workers = self.query(_filter_worker, 'PT_User')
        if len(res_social_workers) > 0:
            for ress in res_social_workers:
                sgj_list.append(
                    {'admin_area_id': ress['admin_area_id'], 'name': ress['name']})

        # 增加社工局返回字段
        _filter.match((C('bill_status') == 'valid'))\
            .lookup_bill('PT_Service_Order', 'order_id', 'id', 'order')\
            .match_bill((C('order.buy_type') == '补贴账户') | (C('order.buy_type') == '慈善账户'))\
            .add_fields({
                'order_purchaser_id': self.ao.array_elemat('$order.purchaser_id', 0),
                'order_service_provider_id': self.ao.array_elemat('$order.service_provider_id', 0),
                'order_code': self.ao.array_elemat('$order.order_code', 0),
                'buy_type': self.ao.array_elemat('$order.buy_type', 0)
            })\
            .lookup_bill('PT_User', 'order_service_provider_id', 'id', 'serviceProvider')\
            .add_fields({
                'service_provider_name': self.ao.array_elemat('$serviceProvider.name', 0)
            })\
            .match_bill((C('serviceProvider.organization_info.personnel_category') == '服务商') & (C('order_service_provider_id') == values['org_id']))\
            .lookup_bill('PT_User', 'order_purchaser_id', 'id', 'user')\
            .match_bill((C('user.admin_area_id').inner(admin_list)))\
            .add_fields({
                'user_name': self.ao.array_elemat('$user.name', 0),
                'user_address': self.ao.array_elemat('$user.personnel_info.address', 0),
                'personnel_classification': self.ao.array_elemat('$user.personnel_info.personnel_classification', 0)
            })\
            .lookup_bill('PT_Personnel_Classification', 'personnel_classification', 'id', 'personnelClassification')\
            .add_fields({
                'classification': self.ao.array_elemat('$personnelClassification.name', 0)
            })\
            .lookup_bill('PT_Service_Product', 'service_product_id', 'id', 'product')\
            .add_fields({
                'product_service_item_id': self.ao.array_elemat('$product.service_item_id', 0),
                'price': '$valuation_amount',
            })\
            .lookup_bill('PT_Service_Item', 'product_service_item_id', 'id', 'item')\
            .add_fields({
                'item_type': self.ao.array_elemat('$item.item_type', 0),
                'item_name': self.ao.array_elemat('$item.name', 0)
            })\
            .lookup_bill('PT_Service_Type', 'item_type', 'id', 'serviceType')\
            .add_fields({
                'item_type_name': self.ao.array_elemat('$serviceType.name', 0),
            })\
            .graph_lookup('PT_Administration_Division', '$user.admin_area_id', 'parent_id', 'id', 'admin')\
            .add_fields({
                "sgj_list": sgj_list
            })\
            .add_fields({'town_info': self.ao.array_filter('$sgj_list', 'tep', (F('$tep.admin_area_id').inner('$admin.id').f))})\
            .add_fields({
                'social_worker': '$town_info.name'
            })\
            .project({'_id': 0, 'admin._id': 0, 'order._id': 0, 'serviceProvider._id': 0, 'user._id': 0, 'personnelClassification._id': 0, 'product._id': 0, 'item._id': 0, 'serviceType._id': 0})
        res = self.page_query(_filter, "PT_Service_Record", page, count)
        return res
